from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        -- 为用户表添加打卡相关字段
        ALTER TABLE `users` ADD COLUMN `is_checkin` BOOL NOT NULL DEFAULT 0 COMMENT '今日是否已打卡';
        ALTER TABLE `users` ADD COLUMN `total_checkin_days` INT NOT NULL DEFAULT 0 COMMENT '总打卡天数';
        ALTER TABLE `users` ADD COLUMN `continuous_checkin_days` INT NOT NULL DEFAULT 0 COMMENT '当前连续打卡天数';
        ALTER TABLE `users` ADD COLUMN `last_checkin_date` DATE COMMENT '最后打卡日期';
        
        -- 创建打卡记录表
        CREATE TABLE IF NOT EXISTS `checkin_records` (
            `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
            `checkin_date` DATE NOT NULL COMMENT '打卡日期',
            `points_awarded` INT NOT NULL DEFAULT 0 COMMENT '本次打卡获得的积分',
            `continuous_days` INT NOT NULL DEFAULT 1 COMMENT '打卡时的连续天数',
            `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
            `user_id` INT NOT NULL COMMENT '关联用户',
            CONSTRAINT `fk_checkin_r_user_b8c8e8` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
            UNIQUE KEY `uid_checkin_re_user_id_b8c8e8` (`user_id`, `checkin_date`)
        ) CHARACTER SET utf8mb4 COMMENT='打卡记录表';
        
        -- 创建索引
        CREATE INDEX `idx_checkin_records_checkin_date` ON `checkin_records` (`checkin_date`);
        CREATE INDEX `idx_checkin_records_user_id` ON `checkin_records` (`user_id`);
    """


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        -- 删除打卡记录表
        DROP TABLE IF EXISTS `checkin_records`;
        
        -- 删除用户表的打卡相关字段
        ALTER TABLE `users` DROP COLUMN `is_checkin`;
        ALTER TABLE `users` DROP COLUMN `total_checkin_days`;
        ALTER TABLE `users` DROP COLUMN `continuous_checkin_days`;
        ALTER TABLE `users` DROP COLUMN `last_checkin_date`;
    """